SQL基础培训

最新推荐文章于 2023-08-22 22:08:53 发布

Mark大先生

最新推荐文章于 2023-08-22 22:08:53 发布

阅读量2.9k

点赞数

文章标签： sql 数据库实例数据索引

SQL基础培训专题

培训目标：了解SQL（select,update,insert）的基本写法、复杂写法，

　　　　　在有索引的情况下，如何写出高效的SQL。

第一章实例环境一描述

学生成绩管理系统

课程信息表 xj_course（CrsID,CrsName, CrsNote ）

学生信息表 xj_stu （StuID, ClsID, StuName, Birthday, Address, Tel, City ）

教师信息表 xj_tea （TeaID, TeaName, address ）

分数表 xj_score （StuID, ClsID, CrsID, TeaID, score ）

课程所属关系信息表 xj_clscrs（ClsID, CrsID,TeaID）

建立环境实例

环境的建立：

MySQL 4.1.10a-max-log, DB2版本。

第二章基本SQL语法介绍

2.1 Select

最基本的语句，MySQL的语法为：

Select [ All | Distinct ]select_list

[ into outFile ‘FileName’ export_options |into dumfile ‘fileName’]

From tabl_list

[Wherewhere_expr ]

[Group by Column desc|asc]

[Having where_expr]

[Order by ColName asc|desc ]

[limit n,m]

[For update |Lock in share mode]

2.1.1,distinct 是控制返回的数据重复行的。

selectdistinct City ,ClsID From xj_stu

select distinct ClsID Fromxj_stu

2，into 选项能把这个结果导出到外面的文本文件中。

mysql>select * into outfile 'wangxl.txt' From xj_stu ;

Query OK, 27 rows affected (0.00 sec)

mysql> select * into dumpfile'xj_stu.txt' From xj_stu ;

ERROR 1172 (42000): Result consisted ofmore than one row

mysql> select * into dumpfile'xj_stu.txt' From xj_stu where stuid='STU001' ;

Query OK, 1 row affected (0.00 sec)

[root@fxs001 /]# find / -name"xj_stu.txt"

/usr/local/mysql-max-4.1.10a-pc-linux-gnu-i686/data/sqldb/xj_stu.txt

3，From tabl_list 列举要检索的数据表或者子查询。

这里要讲究的东西还挺多的，

A,可以设置别名： From xj_stu S, xj_Cls C 。别名别重复、别是关键字就行。

B,可以把一个子查询当作一个表。select * From (select distinct ClsID From xj_stu ) a 。

其实放在 From 后面的东西别理解成表，直接理解成一个结果集，一个通过各种手段

构造的子查询，这样就为写很多复杂的SQL作意识上的准备。

C,联合查询，就在这个中间体现了，一般的有:

tableA A inner join tableB B on A.ID = B.ID 两个都有的来连接。去掉inner也一样。

tableA A left outer join tableB on A.ID = B.ID A中所有的连接起来,去掉outer也一样。

D,USE/IGNORE/FORCE INDEX 来特别提醒MySQL来进行有些索引得取舍。

4,where 子句

是用来限定条件或者建立表、子查询关联的语句。

5，limit 分页中常用

LIMIT 子句可以被用于强制SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)：

mysql> SELECT * FROM table LIMIT 5,10; # 检索记录行 6-15

为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; # 检索记录行 96-last.

如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5; # 检索前 5 个记录行

换句话说，LIMIT n 等价于 LIMIT 0,n。

6，Group by

是用来进行统计分组合计的功能，后面接要进行统计分组的字段或者表达式。

Count(*)

返回由一个 SELECT 语句检索出来的记录行中非 NULL 值的记录总数目：

insert into xj_cls values('9999','tempClass')

select count(S.ClsID) From xj_stu S left join xj_cls C on S.ClsID =C.ClsID ＃28

select count(ClsName) From xj_stu S left join xj_cls C on S.ClsID =C.ClsID ＃27

select count(*) From xj_stu S left join xj_cls C on S.ClsID =C.ClsID ＃28

COUNT(DISTINCT expr,[expr...])

返回一个互不相同的非NULL 的值的总数目：

select count(distinct stuid ) From xj_score; 27

select count(* ) From xj_score ; 81

在 MySQL 中，通过给出一个表达式列表，可以得到不包含 NULL 的不同的表达式组合的数目。

AVG(expr)

返回 expr 的平均值：

select stuID,avg(score) From xj_score Group by StuID ;

select stuID,avg(score) From xj_score where stuid=’12345’ Group by StuID ; (结果条数为0)

MIN(expr)

MAX(expr)

返回 expr 的最小或最大值。MIN() 和 MAX() 可以接受一个字符串参数；在这种情况下，它们将返回最小或最大的字符串传下。查看章节 5.4.3 MySQL 如何使用索引。

select stuID,min(score),max(score) From xj_score Group by StuID

SUM(expr)

返回 expr 的总和。注意，如果返回集中没有从我任何记录行，它将返回 NULL ！

select sum(score) From xj_score whereStuID='001001'

select sum(score) From xj_score whereStuID='001001' Group by StuID

7，Order by 排序

可以指定多个字段排序，可以指定别名、需要进行排序，也可以进行表达式进行排序。

如果继续排序的结果比较多，MySQL会利用临时文件进行排序。

例如：

select * From xj_stu whereClsID='CL001'

Order by (case when StuName='zz'then '00000' else StuID end )

就是要把学生zz排在第一位，其他的按照学号来排列。

2.2 MySQL用于select 和where 子句的函数介绍

http://www.phpe.net/mysql_manual/06-3.html#Other_Functions

大家可以自己去看，如果不属性的话，最好每个都动手写一个sql来试一下，加深一下感情。

这里只列举几个常用的，

(Case when then when ..then else end )

select stuID ,( case when score >=90 then 'A'

when score >= 80 andscore <90 then 'B'

when score >= 70 andscore <80 then 'C'

when score <70 then 'D'

end ) Grad,score

From xj_score

统计一下班级 CL001班的分数中，各个等级的人数分布，

select ( case when score >=90 then 'A'

when score >= 80 andscore <90 then 'B'

when score >= 70 andscore <80 then 'C'

when score <70 then 'D'

end ) Grad ,count(*)

From xj_score

where ClsID='CL001'

Group by 1

Grad count(*)

------- -----------

A 3

B 6

C 3

D 7

换一个格式显示一下，这个格式叫做 “中国财务习惯”，很多地方用到。。

select ClsID,

sum(( case when score >=90 then 1 else 0 end )) A ,

sum(( case when score >= 80 and score <90 then 1 else 0 end )) B ,

sum(( case when score >= 70 and score <80 then 1 else 0 end )) C ,

sum(( case when score <= 70 then 1 else 0 end )) D

From xj_score

Group by ClsID

ClsID A B C D

-------- ---- ---- ---- ----

CL001 3 6 3 7

CL002 6 5 3 10

CL003 3 3 3 6

CL004 5 4 6 6

其实就是传说中的竖的格式转横的格式。

第三章这个学籍管理系统中可能用到的统计需求

要做统计得首先完全清楚这个数据库的设计。请大家再次熟悉一下这个简单的数据库结构。

统计一下各个班级的人数并列举一下来自哪里

Select ClsID,count(*) Cnt ,Group_concat(distinct City) CitySS

From xj_stu

Group by ClsID

ClsID Cnt CitySS

-------- ------ --------------

CL001 7 dl

CL002 8 bj,dl,shanghai

CL003 5 dl,tj

CL004 7 dl,shyang

CL999 1 dalian

注意一下这里的 Group_concat 函数是MySQL特有的，别的数据库没有，得自己写函数来完成这个列到行得转换。

2.3 统计参加考试的学生的均分在　８５分以上的人的清单

select stuID,avg(score) avgScore Fromxj_score sc

where score >=85

Group by stuID

Order by 2 desc

看看　和下面的有什么区别：

select stuID,avg(score) avgScore Fromxj_score sc

Group by stuID

having avg(score) >=85

Order by 2 desc

哪个是正确的　？？

2.4 列一下各班级各科目分数的前3名。

要求结果为：班级、学号、姓名、科目名称、成绩、名次。

selecta.ClsID,a.stuID,stuName,CrsName,a.score,rank From (

select ClsID,CrsID,stuID,score ,(selectcount(*) From xj_score s2 where s2.ClsID=s1.ClsID and s2.CrsID=s1.CrsID ands2.score >=s1.score) Rank

Fromxj_score s1

) a ,xj_stu s,xj_course cc

where Rank <=3 and s.stuID=a.stuID andcc.crsID = a.crsID

Order by a.ClsID,a.CrsID,rank

“名次”　就是排行第几的意思，在MySQL中是弱项，需要通过嵌套子查询来实现，

在 DB2,Orace 中可以使用　分析函数　rank() OVER ( partitionby ClsID,CrsID ORDER BY Score desc)　或者　dense_rank() OVER ( partitionby ClsID,CrsID ORDER BY Score desc)　来实现，这个　rank() 和　dense_rank() 是非常有用的分析函数，在很多时候能解决很多棘手的问题。

课外思考：如何用一个SQL来　找出一个用户流水账户中断档的记录。

表：T_Cash表（CashID,CustomerID,CashAmount,CashBalace）

CID CUSTOMER_ID CASHFLOW_AMOUNT CASH_BALANCE

ASH00000035 70705813 -800 1054228

ASH00000076 70705813 -800 1053428

ASH00000554 70705813 15200 1068542

ASH90001822 70705813 314 1053742

ASH90001823 70705813 -416 1053326

ASH90001824 70705813 314 1053640

ASH90001825 70705813 -416 1053224

ASH90001826 70705813 -416 1052808

ASH90001827 70705813 314 1053122

注意看　CASH_BALANCE　字段的内容是不断变化的，变化的量是当前那条记录的　CashFlowAmount的值，　如果　上图中　红字的那条记录没了，你有什么方法来检查出来是哪条前后的数据对不上了？（可以用　Rank() （只Db2/Oracle中），也可以用子查询来做）

2.5 找出没有参加考试的学生清单。

要求结果为：班级、学号、姓名、缺考科目。

思路分析，首先得构造一个应该考试的清单，它表示全部应该考的内容，然后和已经考试的结果进行左外连接，如果没有匹配到的就是没有考的内容。

select Al.* From

(select s.ClsID,s.StuID,s.StuName,cc.CrsIDFrom xj_stu s,xj_clscrs cc

where s.ClsID=cc.ClsID ) Al left joinxj_score sc on Al.CrsID=sc.crsID and Al.stuID=sc.stuID and Al.ClsID =sc.ClsID

where sc.Stuid is null

ClsID StuID StuName CrsID

-------- -------- ---------- --------

CL001 STU001 zhangyi Crs001

CL001 STU002 zhzhb Crs001

2.6 统计各个班级参加考试的平均分，按照平均分从高到低。

要求结果：班级号、平均分

select clsID,avg(score) From xj_score

Group by ClsID

order by 2 desc

clsID avg(score)

-------- -------------

CL004 76.007619

CL002 73.906667

CL003 73.885333

CL001 73.658421

2.7 统计本次考试各科成绩的及格率，按照及格率从高到低排序。

其实就是分析一下本次考试的整体难度程度，

格式1：科目、及格人数、总考试人数、平均分、及格率。（3行数据）

select sc.CrsID,

cc.CrsName,

sum(case when sc.score>=60 then 1 else 0 end) GCnt,

count(*) CntTotal,

avg(sc.score) avgScore,

sum(case when sc.score>=60 then 1 else 0 end)/Count(*) GCntPercent

From xj_score sc,

xj_course cc

where sc.CrsID =cc.CrsID

Group by sc.CrsID

Order by GCntPercent desc

CrsID CrsName GCnt CntTotal avgScore GCntPercent

-------- ---------- ------- ----------- ----------- --------------

Crs003 English 24 27 75.31963 0.89

Crs001 Chinese 17 25 72.3864 0.68

Crs002 Maths 18 27 75.348889 0.67

格式2：语文及格人数、平均分、及格率，数学及格人数、平均分、及格率，英语及格人数、平均分、及格率。

select

max(Case when CrsName='Chinese' then GCnt else 0 end ) ChGCnt,

max(Case when CrsName='Chinese' then avgScore else 0 end) ChAvg,

max(Case when CrsName='Chinese' then GCntPercent else 0 end )ChGPercent,

max(Case when CrsName='Maths' then GCnt else 0 end ) MaGCnt,

max(Case when CrsName='Maths' then avgScore else 0 end) MaAvg,

max(Case when CrsName='Maths' then GCntPercent else 0 end )MaGPercent,

max(Case when CrsName='English' then GCnt else 0 end ) EnGCnt,

max(Case when CrsName='English' then avgScore else 0 end) EnAvg,

max(Case when CrsName='English' then GCntPercent else 0 end )EnGPercent

From (

select sc.CrsID,

cc.CrsName,

sum(case when sc.score>=60 then 1 else 0 end) GCnt,

count(*) CntTotal,

avg(sc.score) avgScore,

sum(case when sc.score>=60 then 1 else 0 end)/Count(*) GCntPercent

From xj_score sc,

xj_course cc

where sc.CrsID =cc.CrsID

Group by sc.CrsID

) a

ChGCnt ChAvg ChGPercent MaGCnt MaAvg MaGPercent EnGCnt EnAvg EnGPercent

--------- -------- ------------- --------- --------- ------------- --------- -------- -------------

17 72.3864 0.68 18 75.348889 0.67 24 75.31963 0.89

1record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

2.8 年级排名

学号、姓名、语文、数学、英语、平均分、年级名次 (如果有一门没有考则单科算０分)

做法有很多，以下是其中的一种：

selectHA.STUID,HA.STUNAME,HA.CHSCORE,HA.MASCORE,HA.ENSCORE,HB.AVGSCORE,HB.RANK

From

(

select

sc.stuID,s.StuName,

sum((case when cc.CrsName='Chinese' then score else 0 end )) Chscore,

sum((case when cc.CrsName='Maths' then score else 0 end )) Mascore,

sum((case when cc.CrsName='English' then score else 0 end )) Enscore

Fromxj_score sc,xj_course cc,xj_stu s

where sc.CrsID =cc.CrsID and sc.StuID =s.StuID

Group by sc.stuID,s.StuName

) HA, (

select A.stuID,A.avgScore,(

select count(*) From (select sc.stuID,sum(sc.score)/3 avgScore From xj_score sc

Group by sc.StuID ) aa where A.avgScore<=aa.avgScore) Rank

From

(select sc.stuID,sum(sc.score)/3 avgScore From xj_score sc

Group by sc.StuID ) A ) HB

where HA.stuID = HB.stuID

Order by HB.Rank

第四章笛卡尔积的危害和利用

什么是笛卡尔积

　就是有２０条记录的A集合与３０条集合的B集合进行关联，结果因为“一不小心”没有指定关联关键字，那么数据库将用两两组合的形式把最终的结果集返回出来，那么返回的结果就是　20 * 30 ＝600 条。这个“一不小心”的极端情况就是　一个　1万条记录和另一个50万条记录“不小心”进行了笛卡尔积　那就是　50亿条记录，这个对于任何一个数据库来说都是灾难。。。。。

所以写SQL的时候，一定要注意，在表关联的时候，一定要指定能唯一关联的关键字。。

记住的要诀就是 1，一定要关联！2，一定要有一方是唯一的关键字。

一方是唯一的话，那么就是 N * 1 的结果，比较正常。

但有的时候，为了统计的需要，也可以人为的构造这样 N * n 的结果，N是指大的结果集合，n是指特定那么几个常量。

我们来看这个例子：

create table vvList (Code Varchar(20),Amount decimal(10,2))

drop table CODETYPE ;

drop table vvList ;

Create table CODETYPE (Code varchar(10),Name varchar(20) )

select * From CashValue

insert into CODETYPE values ('A','吃' ) ;

insert into CODETYPE values ('A01','蔬菜' ) ;

insert into CODETYPE values ('A02','水果' ) ;

insert into CODETYPE values ('A03','点心' ) ;

insert into CODETYPE values ('B','穿' ) ;

insert into CODETYPE values ('B01','冬衣' ) ;

insert into CODETYPE values ('B02','夏衣' ) ;

insert into CODETYPE values ('B03','秋衣' ) ;

insert into CODETYPE values ('B0301','夹克' ) ;

delete from vvList ;

insert into vvList values ('A0101',1) ;

insert into vvList values ('A0102',2) ;

insert into vvList values ('A0103',3) ;

insert into vvList values ('A0201',4) ;

insert into vvList values ('A0202',5) ;

insert into vvList values ('A0203',6) ;

insert into vvList values ('B0101',7) ;

insert into vvList values ('B0102',8) ;

insert into vvList values ('B0103',9) ;

insert into vvList values ('B0201',11) ;

insert into vvList values ('B0203',17) ;

insert into vvList values ('B0204',788) ;

select substr(Code,1,D.DD) ,

sum(Amount)

From vvList ,

(select distinct length(Code) DD From CODETYPE

union

select 0 ) D

group by substr(Code,1,D.DD)

substr(Code,1,D.DD) sum(Amount)

---------------------- --------------

861

A 21

A01 6

A0101 1

A0102 2

A0103 3

A02 15

A0201 4

A0202 5

A0203 6

B 840

B01 24

B0101 7

B0102 8

B0103 9

B02 816

B0201 11

B0203 17

B0204 788

19record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 07-11-1下午11时49分39秒 ] [Execution:94/ms]

这样的结果在 DB2中有个group byrollup 可以达到类似的功能：

selectsubstr(Code,1,1),substr(Code,1,3),Code,sum(Amount) From vvList

Group byrollup(substr(Code,1,1),substr(Code,1,3),Code)

1 2 3 4

---- ---- ----- ----

All ALL ALL 861

A ALL ALL 21

B ALL ALL 840

A A01 ALL 6

A A02 ALL 15

B B01 ALL 24

B B02 ALL 816

A A01 A0101 1

A A01 A0102 2

A A01 A0103 3

A A02 A0201 4

A A02 A0202 5

A A02 A0203 6

B B01 B0101 7

B B01 B0102 8

B B01 B0103 9

B B02 B0201 11

B B02 B0203 17

B B02 B0204 788

19record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 78/ms]

[Executed: 07-11-1下午11时47分32秒 ][Execution: 110/ms]

也能达到类似的结果。

再来思考下面一个任务，是我们实际工作遇到的，

用户表：Customer(CustomerID) 有50000条记录。

用户配置表：T_LV(SEQ decimal(20,0),CustomerID,ProductID varchar(10))

这个表的特征是每个用户都有一条Product_ID在这里面，现在想增加另一个Product_ID（’T002’）到这个表中，问：如何来用 insert 语句来增加。

注意，是要增加 50000条记录，而且这个seq 不是自增长的，是从原有基础上逐一累加的。

insert into T_LV select T.SEQ +TT.CC,C.CustomerID,'T006' From Cust C,(select Max(Seq) SEQ From T_LV) T,

(select C1.CustomerID,count(*) CC From CustC1,Cust C2 where C1.CustomerID >=C2.CustomerID group by C1.CustomerID) TT

whereC.CustomerID = TT.CustomerID

以是表结构和数据：

id a_id b_id flag

001 a001 b001 Y

002 a001 b002 N

003 a002 b001 Y

004 a002 b002 Y

其中 id,a_id,b_id是主键，

现在要查a_id的个数，要求是：查flag是Y的，重复的算一个，如果相同a_id的不同记录中有N的，不计算入个数。

比如上面的记录中，a001记录中有一条记录flag是N，所以不算，a002中有两条记录，并且falg都是Y,算一条。

则结果应只有1条.

1）

SELECT COUNT(DISTINCTA.A_ID) AID

FROM T1

WHERE a.FLAG= 'Y'

and NOTEXISTS (SELECT 1

FROM T1 B

WHERE A.A_ID = B.A_ID

AND B.FLAG = 'N')

2）

Select count(*)

From (select count(a_id) d

from taby

groupby a_id having min(flag)= 'Y') a;

3）

select sum(Y*N)

From (select A_ID,

max((casewhen Flag = 'Y' then 1 else -99 end)) Y,

min((casewhen Flag = 'N' then 0 else 1 end)) N

From taby

Groupby A_ID) d

分页技术,

Mark大先生

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
SQL基础培训

SQL基础培训专题培训目标：了解SQL（select,update,insert）的基本写法、复杂写法，　　　　　在有索引的情况下，如何写出高效的SQL。第一章实例环境一描述学生成绩管理系统课程信息表 xj_course（CrsID,CrsName, CrsNote ）学生信息表 xj_stu （StuID, ClsID, StuName,
复制链接

扫一扫